﻿using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data;
using System.Data.Common;
using BOHVote.Core;

namespace BOHVote.DAL
{
    public class InterviewProvider
    {
        Database db;
        public InterviewProvider()
        {
            db = DatabaseFactory.CreateDatabase();
        }

        #region <<  select  >>
        public string getQuestionCanUsebyQID(string QID)
        {
            string strSQL = "SELECT Question_CanUse FROM Interview_Question WHERE QID = " + QID;
            DbCommand dbcommand = db.GetSqlStringCommand(strSQL);
            return Utility.ExecuteScalar(db, dbcommand).ToString();
        }

        public DataSet dstGetTotalNumber(string strSubjectID, string strApplyPeriodID, string EvaluationType)
        { 
            if (String.IsNullOrEmpty(EvaluationType))
                EvaluationType = "S";
            string strSQL = "SELECT ar.TotalNumber,ar.ActualNumber FROM ApplyResult ar LEFT JOIN ApplyBase ab ON ar.ApplyID=ab.ApplyID WHERE ab.SubjectID='" + strSubjectID + "' AND ab.ApplyPeriodID='" + strApplyPeriodID + "' AND ar.EvaluationType='" + EvaluationType + "' GROUP BY ar.TotalNumber,ar.ActualNumber";

            DbCommand dbcommand = db.GetSqlStringCommand(strSQL);

            return Utility.ExecuteDataSet(db, dbcommand);
        }


        public DataSet dstGetAllResults(string strApplyName, string strUnitID, string strSubjectID, string strSpecialityID,string strPass,string strType,string strApplyPeriodID,string EvaluationType)
        {
            if (String.IsNullOrEmpty(EvaluationType))
                EvaluationType = "G";

            string strSQL = "SELECT ab.ApplyID,ab.ApplyPrintID,ab.Apply_Name,ab.SubjectID,ab.SpecialityID,ab.UnitID,ab.JobRankID, "
                                  +"   ab.GovernID, ar.EvaluationType, ar.ThesisResult, ar.InterviewResult, ar.TotalNumber, ar.ResultID,ar.ResultSum,"
                                  +"   ar.AgreeNumber, ar.DenyNumber, ar.AbstrainNumber, ar.PassInd, ar.ActualNumber,ar.comment "
                                  + " FROM   v_applyed ab LEFT JOIN (select * from ApplyResult where EvaluationType='" + EvaluationType + "')  ar ON  ab.ApplyID = ar.ApplyID "
                                  +" WHERE 1=1 ";

            if (!string.IsNullOrEmpty(strType) && strType != "-1")
                strSQL += "  AND ab.ApplyStyle = '" + strType + "' ";
            if (!string.IsNullOrEmpty(strApplyName))
                strSQL += "  AND ab.Apply_Name like '%" + strApplyName + "%' ";
            if (!string.IsNullOrEmpty(strUnitID) && strUnitID != "-1")
                strSQL += " AND ab.UnitID = '"+strUnitID+"' ";
            if (!string.IsNullOrEmpty(strSubjectID) && strSubjectID != "-1")
                strSQL += "  AND ab.SubjectID = '" + strSubjectID + "' ";
            if (!string.IsNullOrEmpty(strSpecialityID) && strSpecialityID != "-1")
                strSQL += " AND ab.SpecialityID = '" + strSpecialityID + "' ";
            if (!string.IsNullOrEmpty(strPass) && strPass != "-1")
                strSQL += " AND ar.PassInd = '" + strPass + "' ";
            if (!string.IsNullOrEmpty(strApplyPeriodID))
                strSQL += " AND ab.ApplyPeriodID = '" + strApplyPeriodID + "' ";

            strSQL = strSQL + "  ORDER BY ab.ApplyPrintID, ar.PassInd,ab.SubjectID, ab.SpecialityID,ab.JobRankID,ab.UnitID";
            DbCommand dbcommand = db.GetSqlStringCommand(strSQL);

            return Utility.ExecuteDataSet(db, dbcommand);
        }

        public DataSet dstQuestionByID(string strQID)
        {
            string strSQL = "SELECT * FROM Interview_Question WHERE 1=1 ";
            if (!String.IsNullOrEmpty(strQID))
            {
                strSQL = strSQL + " AND QID=" + strQID ;
            }
            DbCommand dbcommand = db.GetSqlStringCommand(strSQL);

            return Utility.ExecuteDataSet(db, dbcommand);
        }

        public DataSet getAllQuestions(string Content, string strSubject, string strSpecialty,string status,string applyPeriod)
        {
            string strSQL = "SELECT * FROM Interview_Question iq WHERE 1=1 ";

            if (!String.IsNullOrEmpty(Content))
                strSQL = strSQL + " AND iq.Question_Content like '%" + Content + "%' ";

            if (!String.IsNullOrEmpty(strSubject))
                strSQL = strSQL + " AND iq.Question_Subject = '" + strSubject + "' ";

            if (!String.IsNullOrEmpty(strSpecialty) && strSpecialty!="-1")
            {
                strSQL = strSQL + " AND (iq.Question_Specialty ='" + strSpecialty + "') ";
            }
            if (!String.IsNullOrEmpty(applyPeriod) && applyPeriod != "-1")
            {
                strSQL = strSQL + " AND (iq.ApplyPeriodID ='" + applyPeriod + "') ";
            }
            if(!String.IsNullOrEmpty(status))
                strSQL = strSQL + " AND iq.Status = '" + status + "'";
            strSQL = strSQL + " ORDER BY iq.Question_Subject,iq.Question_Specialty,iq.Question_JobRank,iq.QID ";
            DbCommand dbcommand = db.GetSqlStringCommand(strSQL);

            return Utility.ExecuteDataSet(db, dbcommand);
        }

        public DataSet getAllQuestions(string Content, string strSubject, string strSpecialty, string jobRank, string isCommon, string status, string JobLevel, string ApplyPeriodID)
        {
            string strSQL = "SELECT iq.*,jr.JobRankName,jr.JobRankLevel FROM Interview_Question iq LEFT JOIN JobRank jr ON iq.Question_JobRank = jr.JobRankID WHERE 1=1 ";

            if (!String.IsNullOrEmpty(Content))
                strSQL = strSQL + " AND iq.Question_Content like '%" + Content + "%' ";

            if (!String.IsNullOrEmpty(strSubject))
                strSQL = strSQL + " AND iq.Question_Subject = '" + strSubject + "' ";
            if (!String.IsNullOrEmpty(ApplyPeriodID))
            { 
                strSQL = strSQL + " AND iq.ApplyPeriodID = '"+ApplyPeriodID+"'";
            }
            if (String.IsNullOrEmpty(isCommon))
            {
                if (!String.IsNullOrEmpty(strSpecialty))
                    strSQL = strSQL + " AND iq.Question_Specialty ='" + strSpecialty + "' ";
                if (!String.IsNullOrEmpty(jobRank))
                {
                    strSQL = strSQL + " AND (iq.Question_JobRank = '" + jobRank + "' OR iq.Question_JobRank = '-1' ";
                    if (JobLevel == "9")
                        strSQL += " OR iq.Question_JobRank='0' ";
                    else if (JobLevel == "6")
                        strSQL += " OR iq.Question_JobRank='1' ";
                    strSQL += " )";
                }
            }
            else
            {
                if (!String.IsNullOrEmpty(strSpecialty))
                    strSQL = strSQL + " AND iq.Question_Specialty ='" + strSpecialty + "' ";
                if (!String.IsNullOrEmpty(jobRank))
                {
                    strSQL = strSQL + " AND (iq.Question_JobRank = '" + jobRank + "' OR iq.Question_JobRank = '-1' ";
                    if (JobLevel == "9")
                        strSQL += " OR iq.Question_JobRank='0' ";
                    else if (JobLevel == "6")
                        strSQL += " OR iq.Question_JobRank='1' ";
                    strSQL += " )";
                }
            }
            if (!String.IsNullOrEmpty(status))
                strSQL = strSQL + " AND iq.Status = '" + status + "'";
            strSQL = strSQL + " ORDER BY iq.Question_Subject,iq.Question_Specialty,iq.Question_JobRank ";
            DbCommand dbcommand = db.GetSqlStringCommand(strSQL);

            return Utility.ExecuteDataSet(db, dbcommand);
        }
        #endregion

        #region <<  Update  >>
        public int UpdateCanUseByQID(string QID,string Mod)
        {
            string strSQL = "Update Interview_Question Set Question_CanUse = "+Mod+" where QID = "+ QID;
            DbCommand dbcommand = db.GetSqlStringCommand(strSQL);

            return Utility.ExecuteNonQuery(db, dbcommand);
        }

        public int UpdateCanUseBySubject(string SubjectID, string ApplyPeriod,string ModType)
        {
            string strSQL = "Update Interview_Question Set Question_CanUse = " + ModType + " where Question_Subject = '" + SubjectID + "' and ApplyPeriodID = '" + ApplyPeriod + "'";
            DbCommand dbcommand = db.GetSqlStringCommand(strSQL);

            return Utility.ExecuteNonQuery(db, dbcommand);
        }

        public int UpdateStatusQuestions(string Status)
        {
            string strSQL = "Update Interview_Question Set Status = '" + Status + "'";
            DbCommand dbcommand = db.GetSqlStringCommand(strSQL);

            return Utility.ExecuteNonQuery(db, dbcommand);
        }

        public int UpdateQuestions(string QID,string QTitle,string QContent,string QAnswer,int intJobRank,string QSubject,string QSpecialty,string strStatus,string subSpecialty)
        {
            string strSQL = " UPDATE Interview_Question SET Question_Title = '" + QTitle + "',Question_Content = '" + QContent + "',Question_Answer = '" + QAnswer + "',Question_JobRank= " + intJobRank + ",Question_Subject='" + QSubject + "',Question_Specialty='" + QSpecialty + "',status='" + strStatus + "',Question_SubSpecialty='" + subSpecialty + "'  WHERE QID = " + QID + " ";
            DbCommand dbcommand = db.GetSqlStringCommand(strSQL);

            return Utility.ExecuteNonQuery(db, dbcommand);
        }

        public int UpdateStatus(string QID, string Status)
        {
            string strSQL = " UPDATE Interview_Question SET Status='" + Status + "' WHERE QID = " + QID;
            DbCommand dbcommand = db.GetSqlStringCommand(strSQL);

            return Utility.ExecuteNonQuery(db, dbcommand);
        }

        public string CheckResultApplyID(string ApplyID, string EvaluationType)
        {
            string strSQL = "SELECT resultID FROM ApplyResult WHERE ApplyID = '" + ApplyID + "' AND EvaluationType = '" + EvaluationType + "' ";
            DbCommand dbcommand = db.GetSqlStringCommand(strSQL);
            int intCount = 0;
            int.TryParse(Convert.ToString(Utility.ExecuteScalar(db, dbcommand)), out intCount);
            return intCount.ToString() ;//((int)Utility.ExecuteScalar(db, dbcommand)).ToString();
        }

        public int InsertInsterviewResult(string ApplyID, string EvaluationType,string ResultSum, string TotalNumber, string AgreeNumber, string DenyNumber, string AbstrainNumber, string comment, string ActualNumber)
        {
            string isPassed = "N";
            int intAgree = 0;
            int intTotalNumber = 0;
            int intAbstrainNumber = 0;
            int intActualNumber = 0;
            int intDeny = 0;
            if (!String.IsNullOrEmpty(AgreeNumber))
                intAgree = Convert.ToInt32(AgreeNumber);
            if (!String.IsNullOrEmpty(TotalNumber))
                intTotalNumber = Convert.ToInt32(TotalNumber);
            if (!String.IsNullOrEmpty(AbstrainNumber))
                intAbstrainNumber = Convert.ToInt32(AbstrainNumber);
            if (!String.IsNullOrEmpty(ActualNumber))
                intActualNumber = Convert.ToInt32(ActualNumber);
            if (!String.IsNullOrEmpty(DenyNumber))
                intDeny = Convert.ToInt32(DenyNumber);
            //intAbstrainNumber += intTotalNumber - intActualNumber;

            if (Convert.ToDouble(intAgree) / Convert.ToDouble(intTotalNumber) > 0.5)
                isPassed = "Y";
            else
                isPassed = "N";

            string strSQL = "INSERT INTO ApplyResult(ApplyID,EvaluationType,ResultSum,TotalNumber,AgreeNumber,DenyNumber,AbstrainNumber,PassInd,comment,ActualNumber) ";
            strSQL += " VALUES(" + ApplyID + ",'" + EvaluationType + "','" + ResultSum + "'," + intTotalNumber + "," + intAgree + "," + intDeny + "," + intAbstrainNumber + ",'" + isPassed + "','" + comment + "'," + intActualNumber + ")";
            DbCommand dbcommand = db.GetSqlStringCommand(strSQL);

            return Utility.ExecuteNonQuery(db, dbcommand);
        }

        public int UpdateInterviewResult(string ResultID, string ResultSum,string TotalNumber, string AgreeNumber, string DenyNumber, string AbstrainNumber, string comment, string ActualNumber)
        {
            string isPassed = "N";
            int intAgree = 0;
            int intTotalNumber = 0;
            int intAbstrainNumber = 0;
            int intActualNumber = 0;
            int intDeny = 0;
            if (!String.IsNullOrEmpty(AgreeNumber))
                intAgree = Convert.ToInt32(AgreeNumber);
            if (!String.IsNullOrEmpty(TotalNumber))
                intTotalNumber = Convert.ToInt32(TotalNumber);
            if (!String.IsNullOrEmpty(AbstrainNumber))
                intAbstrainNumber = Convert.ToInt32(AbstrainNumber);
            if (!String.IsNullOrEmpty(ActualNumber))
                intActualNumber = Convert.ToInt32(ActualNumber);
            if (!String.IsNullOrEmpty(DenyNumber))
                intDeny = Convert.ToInt32(DenyNumber);
            //intAbstrainNumber += intTotalNumber - intActualNumber;

            if (Convert.ToDouble(intAgree) / Convert.ToDouble(intTotalNumber) > 0.5)
                isPassed = "Y";
            else
                isPassed = "N";

            string strSQL = "UPDATE ApplyResult SET ResultSum='" + ResultSum + "',TotalNumber = " + intTotalNumber + ",AgreeNumber =" + intAgree + ",DenyNumber = " + intDeny + ",AbstrainNumber = " + intAbstrainNumber + ",PassInd = '" + isPassed + "',comment ='" + comment + "',ActualNumber = " + intActualNumber + " WHERE resultID=" + ResultID;

            DbCommand dbcommand = db.GetSqlStringCommand(strSQL);

            return Utility.ExecuteNonQuery(db, dbcommand);
        }


        #endregion

        #region <<  Insert  >>

        public int InsertQuestionCheck(string QID, string UserID, string SubjectID,string ApplyID)
        {
            string strSQL = "INSERT INTO Question_Check(CheckDatetime,CheckQuetionID,UserID,SubjectID,ApplyID)VALUES('" + DateTime.Now.ToShortDateString() + "'," + QID + "," + UserID + ",'" + SubjectID + "'," + ApplyID.Trim() + ")";
            DbCommand dbcommand = db.GetSqlStringCommand(strSQL);

            return Utility.ExecuteNonQuery(db, dbcommand);
        }

        public int InsertQuestions(string QTitle, string QContent, string QAnswer, int intJobRank, string QSubject, string QSpecialty, string strStatus, string subSpecialty, string ApplyPeriod)
        {
            string strSQL = "INSERT INTO Interview_Question(Question_Title,Question_Content,	Question_Answer,Question_JobRank,Question_Subject,Question_Specialty,[status],Question_SubSpecialty,ApplyPeriodID)";
            strSQL += "VALUES('" + QTitle + "','" + QContent + "','" + QAnswer + "','" + intJobRank + "','" + QSubject + "','" + QSpecialty + "','" + strStatus + "','" + subSpecialty + "','" + ApplyPeriod + "')";
            DbCommand dbcommand = db.GetSqlStringCommand(strSQL);

            return Utility.ExecuteNonQuery(db, dbcommand);
        }
        #endregion

        #region <<  Delete  >>
        public int DeleteQuestions(string QID)
        {
            string strSQL = "DELETE FROM Interview_Question WHERE QID = " + QID;

            DbCommand dbcommand = db.GetSqlStringCommand(strSQL);

            return Utility.ExecuteNonQuery(db, dbcommand);

        }
        #endregion
    }
}
